﻿Imports System
Imports System.Data
Imports System.Configuration
Imports System.Collections
Imports System.Web
Imports System.Web.Security
Imports System.Web.UI
Imports System.Web.UI.WebControls
Imports System.Web.UI.WebControls.WebParts
Imports System.Web.UI.HtmlControls
Imports Microsoft.Reporting.WebForms
Imports log4net

Partial Class Report_RP02_2
    Inherits System.Web.UI.Page
    Public ScriptText As String
    Dim Report As Object
    Private Shared logger As ILog = LogManager.GetLogger("Report_RP01_2")

    Protected Sub Page_Init(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Init

        'If Session("fundcenterS") = "" Then
        '    Session("fundcenterS") = "0011"
        '    Session("fundcenterE") = "0012"

        'End If

        'If Session("users") = "" Then
        '    Session("users") = "wassana"
        'End If
        'Session("year") = 2556
        'Session("version") = 2

        If Session("users") Is Nothing Then
            Response.Redirect("../LoginForm.aspx")
        End If


        Getdata()


    End Sub




    Function Getdata() As Decimal


        Dim adapter As New Data_ReportTableAdapters.R02TableAdapter
        Dim sql As String
        Dim dt As New Data_Report.R02DataTable


        sql = " select"

        sql += "  CASE ISNULL(dt.Project_ProjectCode,'') WHEN '' THEN N'งบประมาณประจำ' ELSE (CASE LEFT(dt.Project_ProjectCode,1) WHEN 'P' THEN 'งบประมาณโครงการ' WHEN 'D' THEN 'งบประมาณสนับสนุน' END) END AS BudgetType, "
        sql += "  CASE ISNULL(dt.ActivityType_ActivityTypeCode,'')+ISNULL(dt.AssetNo,'') WHEN '' THEN N'รายจ่ายดำเนินงาน' ELSE N'รายจ่ายลงทุน' END AS AssetType, "

        sql += "  ISNULL(dt.Project_ProjectCode,'') as ProjectCode, "

        sql += "  ISNULL(p.Description,'') as ProjectDescription, "

        sql += "  h1.Description,   h2.Description, (Cmmts.CmmtCode + '  ' + Cmmts.Description) as Description,"

        sql += "  SUM(dt.Amount)"

        sql += "  from BudgetDetails dt"

        sql += " inner join FundCenters on"

        sql += " FundCenters.FundCenterCode = dt.FundCenter_FundCenterCode And FundCenters.PlanYear = dt.FundCenter_PlanYear "

        sql += " inner join Cmmts on"

        sql += "  Cmmts.CmmtCode = dt.Cmmt_CmmtCode And Cmmts.PlanYear = dt.Cmmt_PlanYear "

        sql += " inner join CmmtHierarchies h3 on "

        sql += " h3.Cmmt_CmmtCode = dt.Cmmt_CmmtCode and h3.Cmmt_PlanYear = dt.Cmmt_PlanYear "

        sql += " and h3.CmmtGroup = '1'"

        sql += " inner join CmmtHierarchies h2 on"

        sql += "  h3.ParentCmmtHierarchy_Id = h2.Id "

        sql += " and h2.CmmtGroup = '1'"

        sql += " inner join CmmtHierarchies h1 on "

        sql += "  h2.ParentCmmtHierarchy_Id = h1.Id "

        sql += " and h1.CmmtGroup = '1'"

        sql += " left join Projects p on"

        sql += "  (p.ProjectCode = dt.Project_ProjectCode and"

        sql += "  p.PlanYear = dt.Project_PlanYear and p.FundCenterCode = dt.Project_FundCenterCode ) or p.ProjectCode IS NULL"


        sql += " where  dt.PlanYear  = " & Session("year") & " and Version = '" & Session("version") & "'"

        If Session("FCTRmode") = "1" Then
            sql += " and  dt.FundCenter_FundCenterCode IN (" & Session("FundcenterAll") & ") "
        ElseIf Session("FCTRmode") = "2" Then
            sql += " and  dt.FundCenter_FundCenterCode IN (" & Session("FundcenterAll") & ") "
        End If

        If Session("CmmtMode") = "1" Then
            If Val(Session("CmmtCodeS")) <> 0 And Val(Session("CmmtCodeE")) <> 0 Then
                sql += " and  Cmmts.CmmtCode  between " & Session("CmmtCodeS") & " and " & Session("CmmtCodeE")
            End If
            If Val(Session("CmmtCodeS")) <> 0 And Val(Session("CmmtCodeE")) = 0 Then
                sql += " and  Cmmts.CmmtCode = " & Session("CmmtCodeS")
            End If
            If Val(Session("CmmtCodeS")) = 0 And Val(Session("CmmtCodeE")) <> 0 Then
                sql += " and  Cmmts.CmmtCode = " & Session("CmmtCodeE")
            End If
            If Val(Session("CmmtCodeS")) = 0 And Val(Session("CmmtCodeE")) = 0 Then
                sql += " and  Cmmts.CmmtCode  between " & "'00000000'" & " and " & "'99999999'"
            End If
        ElseIf Session("CmmtMode") = "2" Then
            If Session("CmmtParent") <> "" And Session("CmmtParent") <> "0" Then
                sql += "and cmmtcode in (select cmmt_cmmtcode from  cmmthierarchies where parentcmmthierarchy_id in (select id from cmmthierarchies "
                sql += " where id = " & Session("CmmtParent") & " or parentcmmthierarchy_id = " & Session("CmmtParent") & ")"
                sql += " or id in (select id from cmmthierarchies "
                sql += " where id = " & Session("CmmtParent") & " or parentcmmthierarchy_id = " & Session("CmmtParent") & ")"
                sql += ")"
            End If
        End If


        sql += " Group by"

        sql += " dt.FundCenter_PlanYear,"

        sql += " dt.Project_ProjectCode,p.Description,dt.ActivityType_ActivityTypeCode,dt.AssetNo,"

        sql += "  h1.Id,h1.Description, "

        sql += "  h2.Id,h2.Description, "

        sql += " dt.Cmmt_PlanYear, dt.Cmmt_CmmtCode, Cmmts.Description,Cmmts.CmmtCode "

        sql += " Order By  "
        sql += " CASE ISNULL(dt.Project_ProjectCode,'') WHEN '' THEN N'1' ELSE (CASE LEFT(dt.Project_ProjectCode,1) WHEN 'P' THEN N'2' WHEN 'D' THEN N'3' END) END, "
        sql += " CASE ISNULL(dt.ActivityType_ActivityTypeCode,'')+ISNULL(dt.AssetNo,'') WHEN '' THEN N'1' ELSE N'2' END, "
        sql += " dt.Project_ProjectCode, "
        sql += " dt.ActivityType_ActivityTypeCode, "
        sql += " Cmmts.CmmtCode"


        ''Response.Write(sql)
        ''Exit Function
        adapter.SearchNews(dt, sql)


        Dim das As New DataSet
        dt.TableName = "R02DT2"
        das.DataSetName = "R01DS"
        das.Tables.Add(dt)
        ReportViewer1.ProcessingMode = Microsoft.Reporting.WebForms.ProcessingMode.Local
        'ReportViewer1.LocalReport.ReportPath = System.Environment.CurrentDirectory & "Report\R01.rdlc"
        ReportViewer1.LocalReport.DataSources.Clear()
        ReportViewer1.LocalReport.DataSources.Add(New Microsoft.Reporting.WebForms.ReportDataSource("DataSet1", das.Tables("R02DT2")))
        ReportViewer1.DocumentMapCollapsed = True

        Dim UserName As String = Session("users")
        Dim years As String = Session("year")
        Dim version As String = Session("version")
        Dim users As String = Session("users")

        Dim p1, p2, p3 As ReportParameter
        p1 = New ReportParameter("year", years)
        p2 = New ReportParameter("version", version)
        p3 = New ReportParameter("username", UserName)

        ReportViewer1.LocalReport.SetParameters(p1)
        ReportViewer1.LocalReport.SetParameters(p2)
        ReportViewer1.LocalReport.SetParameters(p3)


        ReportViewer1.LocalReport.Refresh()





        'Response.Write(dt.Rows.Count & sql)
        Session("count") = dt.Rows.Count



        Return dt.Rows.Count

    End Function
    Public Function nPageNumber() As String
        Dim str As String
        str = Me.Report.Globals!PageNumber.ToString()
        Return str
    End Function

    Protected Sub Page_Load(sender As Object, e As EventArgs) Handles Me.Load
        If Session("users") Is Nothing Then
            ScriptText = "<script> wclose();</script>"
        End If
    End Sub
End Class
